# -*- coding: utf-8 -*-
""" Database handling routines """

__all__ = ['databases']
__author__ = 'Sergey Sobko, Timir Nureev'
__copyright__ = 'Copyright (c) 2010, DEKO PROFIT (The ProfitWare Project)'
__license__ = 'BSD'
__credits__ = ['Sergey Sobko', 'Timir Nureev']
__email__ = 'info@dekoprofit.ru'
__status__ = 'Development'

from ProfitDCS import concat_keys, re_s
from ProfitDCS.security import md5sum
from logging import warning as w

use_memcache = False
db_providers = []

try:
	import MySQLdb as m
	db_providers += ['mysql']
except ImportError:
	pass

try:
	import sqlite3 as s
	db_providers += ['sqlite3']
except ImportError:
	pass

try:
	import memcache
	mc = memcache.Client(['127.0.0.1:11211'], debug=0)
	mc.set('__memcache_test', 'true')
	assert mc.get('__memcache_test') == 'true'
	import cPickle
	mc_sql_p = '(?:UPDATE|FROM|INTO)(?: )(\w+)'
	use_memcache = True
except Exception:
	w('ProfitDCS:db / Not using memcached')
	pass

class xdict(dict):
	arg = None
	def __init__(self, arg = None):
		self.arg = arg
	def __getattr__(self, x):
		try:
			return self.__getattribute__(x)
		except Exception:
			pass
		if not x.startswith('__sql_'):			
			try:
				return self['__sql_' + x]
			except Exception:
				pass
		else:
			x = x[6:]
		return self[x]

class databases(xdict):
	__db = {}
	databases = xdict()
	__query_roles = None
	__usingdb = ''
	__oe = __pe = Exception
	qnum, qc = 0, 0
	checkdb = lambda self, dbid, db_prov: self.databases[dbid].has_key(db_prov) and (db_prov in db_providers)
	db = lambda self: self.databases
	def __sql_opendb(self, dbid, lc = False):
		if not self.__db.has_key(dbid) or lc:
			if self.checkdb(dbid, 'mysql'):
				A = self.databases[dbid]['mysql'].split(';')		
				conn = m.connect('%s' % A[0], '%s' % A[1], '%s' % A[2], '%s' % A[3])
				self.__usingdb = 'mysql'
				self.__oe, self.__pe = m.OperationalError, m.ProgrammingError
			elif self.checkdb(dbid, 'sqlite3'):
				conn = s.connect(self.databases[dbid]['sqlite3'])
				self.__usingdb = 'sqlite3'
				self.__oe, self.__pe = s.OperationalError, s.ProgrammingError
			else:
				return None
			self.__db.update({ dbid: { 'conn': conn, 'cur': conn.cursor() }})
		else:
			self.__db[dbid]['conn'].commit()
		return self.__db[dbid]['cur']

	def get_qhash(self, dbid, qid):
		dbdsn = self.databases[dbid][self.__usingdb]
		if self.__usingdb == 'mysql':
			A = dbdsn.split(';')
			dbname = '%s;%s' % (A[0], A[3])
		else:
			dbname = dbdsn
		x = 'pdcs_dbtable:%s:%s' % tuple(map(md5sum, [dbname, re_s(mc_sql_p, self.databases[dbid][qid]).group(1)]))
		return x

	update_mc_cache = lambda self, *args: use_memcache and mc.set(self.get_qhash(*args), True)
		
	def sql_query(self, dbid, qid, data='', inlinefunc=None, needcommit=False):
		self.qnum += 1
		for i in [1, 2]:
			try:
				func = type(inlinefunc).__name__ == 'function' and inlinefunc or (lambda x: x)
				dosql = True
				if use_memcache:
					qhash = self.get_qhash(dbid, qid)
					ai = md5sum(cPickle.dumps((dbid, qid, data, repr(func))))
					f = mc.get(ai)
					dosql = (not f) or mc.get(qhash)
				if dosql:		
					query = self.databases[dbid][qid]
					if reduce(bool.__or__, [(i in query) for i in ['DELETE', 'INSERT', 'UPDATE']]):
						needcommit = True
					f = ()
					for lc in [False, True]:
						cur = self.__sql_opendb(dbid, lc)
						try:
							data == '' and cur.execute(query) or cur.execute(query, data)
							if needcommit:
								self.__db[dbid]['conn'].commit()
								if use_memcache:
									mc.set(qhash, True)
							f = func(cur.fetchall())
							if use_memcache and not needcommit:
								mc.set(ai, f)
								mc.delete(qhash)
						except self.__oe:
							w('ProfitDCS:db / OperationalError while querying %s:%s, lost connection = %s' % (dbid, qid, lc))
							continue
						break
				else:
					self.qc += 1
				return f
			except IndexError:
				w('ProfitDCS:db / IndexError while querying %s:%s' % (dbid, qid))
				pass
			except self.__oe, e:
				w('ProfitDCS:db / OperationalError while querying %s:%s (%s)' % (dbid, qid, e))
				try:
					self.__db[dbid].close()
				except:
					pass
				self.__sql_opendb(dbid, True)
				w('ProfitDCS:db / OperationalError fixed (reconnected)')
				continue
			except self.__pe, e:
				w('ProfitDCS:db / ProgrammingError: %s' % e)
			except Exception, e:
				raise Exception, e
			break
		return tuple()
	
	def __init__(self, x, um = False):
		global use_memcache
		use_memcache = use_memcache and um
		self.qnum, self.qc = 0, 0
		roles_dict = {}
		for database in x.get_element('/project/database'):
			dbid = database['database']['id']
			self.databases.update({dbid: xdict()})
			self.__usingdb = db_providers[0]
			for i in db_providers:
				try:
					self.databases[dbid].update({i: database['database'][i], '__dbid': dbid})
				except:
					pass
			try:
				[[self.databases[s].update((d[0] + i.attr('id'), i.attr(d[1])) for i in x.get_element('/project/database[id=%s]/%s' % (s, d[2],))) for s in self.databases.keys()] for d in [('', 'value', 'query'), ('fields_', 'value', 'fields'), ('fields_wdt_', 'width', 'fields')]]
			except Exception, e:
				w('ProfitDCS:db / Exception while initializing db: %s' % e)
				pass
			try:
				[[self.databases[i].update({'__sql_' + q: (lambda xdbid, xqid: lambda *args: self.sql_query(xdbid, xqid, *args))(i, q)}) for q in filter(lambda x: x not in db_providers + ['__dbid'], self.databases[i].keys())] for i in self.databases.keys()]
			except:
				w('ProfitDCS:db / Exception while creating db functions: %s' % e)
				pass
			self.update(self.databases)
	
	def sql_form_csv(self, dbid, qid, data='', rowfunc = None, delim=','):
			rowfunc = type(rowfunc).__name__ == 'function' and rowfunc or (lambda x: x)
			form_csv = lambda fetched: concat_keys([rowfunc(concat_keys(a, '', delim)) for a in fetched], '', '\n')
			query = self.databases[dbid][qid]
			hKeys = concat_keys(query[query.find('SELECT') + 7:query.find('FROM') - 1].split(', '), '', delim) + '\n'	
			return hKeys + str(self.sql_query(dbid, qid, data, form_csv))
